package com.smartjobs.model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.smartjobs.bean.EducationalQualificationBean;
import com.smartjobs.bean.JobseekerBean;
import com.smartjobs.bean.SkillMasterBean;
import com.smartjobs.bean.SkillsBean;
import com.smartjobs.bean.WorkExperienceBean;
import com.smartjobs.util.DBManager;

public class ViewJSProfileModel {

	public static void GetJSProfile(JobseekerBean jobseeker) {

		String query = "SELECT * FROM jobseeker where jobSeekerId = "
				+ jobseeker.getJobSeekerID();

		ResultSet result = DBManager.selectFromDB(query);

		try {
			if (result.next()) {

				SimpleDateFormat format2 = new SimpleDateFormat("MM/dd/yyyy");
				SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
				String dateOfBirth = "";
				// Date date = null;
				try {
					Date date = format1.parse(result.getString("dob"));
					dateOfBirth = format2.format(date);
					jobseeker.setDob(dateOfBirth);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				jobseeker.setMobileNumber(Integer.parseInt(result
						.getString("mobileNumber")));
				jobseeker.setName(result.getString("name"));
				jobseeker.setEmailID(result.getString("emailId"));

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void GetJSEQ(int jobseekerID,
			ArrayList<EducationalQualificationBean> educationalQualificationList) {

		EducationalQualificationBean educationalQualification;

		String query = "SELECT * FROM smartjobs.educational_qual where jobSeekerId = "
				+ jobseekerID;

		ResultSet result = DBManager.selectFromDB(query);

		try {
			while (result.next()) {
				educationalQualification = new EducationalQualificationBean();
				educationalQualification.setAggregateMarks(result.getString(4));
				educationalQualification.setDegreeName(result.getString(2));
				educationalQualification.setEducationalQualificationID(Integer
						.parseInt(result.getString(1)));
				educationalQualification.setJobseekerID(Integer.parseInt(result
						.getString(5)));
				educationalQualification.setYearOfPassing(result.getString(3));
				educationalQualificationList.add(educationalQualification);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void GetJSWorkEx(int jobseekerID,
			ArrayList<WorkExperienceBean> workExperienceList) {
		WorkExperienceBean workExperience;

		String query = "SELECT * FROM smartjobs.experience where jobSeekerId = "
				+ jobseekerID;

		ResultSet result = DBManager.selectFromDB(query);

		try {
			while (result.next()) {
				workExperience = new WorkExperienceBean();
				workExperience.setCompanyName(result.getString(2));
				workExperience.setExpID(Integer.parseInt(result.getString(1)
						.toString()));
				workExperience.setJobseekerID(Integer.parseInt(result
						.getString(4).toString()));
				workExperience.setYears(result.getString(3));
				workExperienceList.add(workExperience);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public static void GetJSSkills(int jobseekerID,
			ArrayList<SkillsBean> skillList) {

		SkillsBean skill;

		String query = "select * from (SELECT j.jssId,j.jobSeekerId,j.skillId,"
				+ "j.noOfMonths,s.skillName FROM smartjobs.jobseeker_skills as "
				+ "j inner join smartjobs.skills as s where j.skillId = s.skillId)"
				+ " as js where js.jobSeekerId = " + jobseekerID;

		ResultSet result = DBManager.selectFromDB(query);

		try {
			while (result.next()) {
				skill = new SkillsBean();
				skill.setJobseekerID(Integer.parseInt(result.getString(2)
						.toString()));
				skill.setJssID(Integer.parseInt(result.getString(1).toString()));
				skill.setMonths(result.getString(4));
				skill.setSkillID(Integer.parseInt(result.getString(3)
						.toString()));
				skill.setSkillName(result.getString(5));
				skillList.add(skill);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

	public static void GetSkillMasterList(
			ArrayList<SkillMasterBean> skillMasterList) {
		SkillMasterBean skillMaster;
		String query = "SELECT * FROM smartjobs.skills";
		ResultSet ddList = DBManager.selectFromDB(query);

		try {
			while (ddList.next()) {
				skillMaster = new SkillMasterBean();
				skillMaster.setSkillID(Integer.parseInt(ddList.getString(1)
						.toString()));
				skillMaster.setSkillName(ddList.getString(2));
				skillMasterList.add(skillMaster);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
	}
}
